package com.first.dao.impl;

import com.first.dao.BOrderDao;
import com.first.entity.BOrder;
import com.first.entity.BOrderWj;
import com.first.util.DBUtil;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository
public class BOrderDaoImpl implements BOrderDao {
    /**
     * 查询订单详情根据orderid
     *
     * @param bOrder
     * @return
     */
    @Override
    public List<Map> getBOrderById(BOrder bOrder) {

        StringBuilder stringBuilder = new StringBuilder("SELECT t_price ,o.orderid ,b.bookid,bookname ,author, press, b.inprice ,w.b_count  ,w.count from book b,b_order o,b_order_wj w where b.bookid=w.bookid and o.orderid=w.orderid ");
        List<Map> list;
        if (bOrder.getOrderid() != null) {
            stringBuilder.append(" and w.orderid = ?");
            list = DBUtil.queryListMap(stringBuilder.toString(), bOrder.getOrderid());
        } else {
            list = DBUtil.queryListMap(stringBuilder.toString());
        }
        if (list.size() > 0) {
            return list;
        } else {
            return null;
        }
    }

    /**
     * 查询所有订单
     *
     * @param bOrder
     * @return
     */
    @Override
    public List<Map> getAllBOrder(BOrder bOrder) {
        StringBuilder builder = new StringBuilder("SELECT *  from b_order  where  orderstatus= 1");
        List<Map> maps;

        if (bOrder.getE_name() != null && !"".equals(bOrder.getE_name())) {
            builder.append(" and   e_name like  '%" + bOrder.getE_name() + "%'");
        }
        if (bOrder.getW_name() != null && !"".equals(bOrder.getW_name())) {
            builder.append(" and   e_name like  '%" + bOrder.getW_name() + "%'");
        }
        if (bOrder.getOrderdate() != null && !"".equals(bOrder.getOrderdate())) {
            builder.append(" and   e_name like  '%" + bOrder.getOrderdate() + "%'");
        }
            builder.append(" limit ?,?");
            maps = DBUtil.queryListMap(builder.toString(),(bOrder.getPage() - 1) * bOrder.getLimit(), bOrder.getLimit());

        if (maps.size() > 0) {

            return maps;
        } else {
            return null;
        }
    }
    /**
     * 查询入库审核所有订单
     *
     * @param bOrder
     * @return
     */
    @Override
    public List<Map> getAllBOrder1(BOrder bOrder) {
        StringBuilder builder = new StringBuilder("SELECT *  from b_order where  orderstatus= 3");
        List<Map> maps;
        if (bOrder.getOrderid() != null && !"".equals(bOrder.getOrderid())) {
            builder.append(" and orderid like '%" + bOrder.getOrderid() + "%'  ");
            maps = DBUtil.queryListMap(builder.toString());
        } else {

            maps = DBUtil.queryListMap(builder.toString());
        }
        if (maps.size() > 0) {

            return maps;
        } else {
            return null;
        }
    }




    /**
     * 查询入库审核所有订单
     *
     * @param bOrder
     * @return
     */
    @Override
    public List<Map> getAllBOrder4(BOrder bOrder) {
        StringBuilder builder = new StringBuilder("SELECT *  from b_order where orderstatus=4");
        List<Map> maps;
        if (bOrder.getP_name() != null && !"".equals(bOrder.getP_name())) {
            builder.append(" and p_name like '%" + bOrder.getP_name() + "%'");

        }else if (bOrder.getW_name() != null && !"".equals(bOrder.getW_name())) {
            builder.append(" and w_name like '%" + bOrder.getW_name() + "%'");

        }else if (bOrder.getE_name() != null && !"".equals(bOrder.getE_name())) {
            builder.append(" and e_name like '%" + bOrder.getE_name() + "%'");

        }else if (bOrder.getStoragedate() != null && !"".equals(bOrder.getStoragedate())) {
            builder.append(" and storagedate like '%" + bOrder.getStoragedate()  + "%'");

        }
        builder.append("  order by storagedate DESC limit ?,?");
//        builder.append(" limit ?,?");
        maps = DBUtil.queryListMap(builder.toString(),(bOrder.getPage() - 1) * bOrder.getLimit(), bOrder.getLimit());

        if (maps.size() > 0) {

            return maps;
        } else {
            return null;
        }
    }

    /**
     * 查询入库书籍的信息的数量
     * @param bOrder
     * @return
     */
    @Override
    public int getAllBOrderCount4(BOrder bOrder) {
        StringBuilder builder = new StringBuilder("SELECT count(*)  from b_order where orderstatus=4");
        if (bOrder.getP_name() != null && !"".equals(bOrder.getP_name())) {
            builder.append(" and p_name like '%" + bOrder.getP_name() + "%'");

        }else if (bOrder.getW_name() != null && !"".equals(bOrder.getW_name())) {
            builder.append(" and w_name like '%" + bOrder.getW_name() + "%'");

        }else if (bOrder.getE_name() != null && !"".equals(bOrder.getE_name())) {
            builder.append(" and e_name like '%" + bOrder.getE_name() + "%'");

        }else if (bOrder.getStoragedate() != null && !"".equals(bOrder.getStoragedate())) {
            builder.append(" and storagedate like '%" + bOrder.getStoragedate()  + "%'");

        }
        builder.append(" order by storagedate DESC");
        List<List> lists =  DBUtil.queryList(builder.toString());
        Object o = lists.get(0).get(0);
        int i = Integer.parseInt(o + "");
        return i;
    }


    /**
     * 获得订单数量
     *
     * @param bOrder
     * @return
     */
    @Override
    public int getAllBOrderCount(BOrder bOrder) {
        StringBuilder sql = new StringBuilder("SELECT count(*) from book b,b_order o,b_order_wj w where b.bookid=w.bookid and o.orderid=w.orderid ORDER BY b.bookid");
        List<List> lists =  DBUtil.queryList(sql.toString());
        Object o = lists.get(0).get(0);
        int i = Integer.parseInt(o + "");
        return i;
    }

//    /**
//     * 通过id修改
//     *
//     * @param bOrder
//     * @return
//     */
//    @Override
//    public int updateById(BOrder bOrder) {
//        String sql = "update b_order set inprice=?,plancount=?,orderstatus=?,orderdate=?,p_id=?,w_id=?,e_id=? where orderid=?";
//        int i = DBUtil.update(sql, bOrder.getInprice(), bOrder.getPlancount(), bOrder.getOrderstatus(), bOrder.getOrderdate(), bOrder.getOrderid());
//        return i;
//    }

    /**
     * 新增订单
     *
     * @param bOrder
     * @return
     */
    @Override
    public int addOrder(BOrder bOrder) {

        String sql = "insert into b_order values(?,?,0,?,null,?,null,null,?,null,null)";
        int i = DBUtil.update(sql, bOrder.getOrderid(), bOrder.getPlancount(), bOrder.getOrderdate(), bOrder.getW_name(),bOrder.getB_t_price());
        return i;
    }

    /**
     * 查看所有驳回订单
     *orderstatus= 2
     * @param bOrder
     * @return
     */
    @Override
    public List<Map> queryReject(BOrder bOrder) {
        StringBuilder builder=new StringBuilder("SELECT * FROM b_order where orderstatus= 2");
        if (bOrder.getW_name() != null && !"".equals(bOrder.getW_name())) {
            builder.append(" and w_name like '%" + bOrder.getW_name() + "%'");

        }else if (bOrder.getE_name() != null && !"".equals(bOrder.getE_name())) {
            builder.append(" and e_name like '%" + bOrder.getE_name() + "%'");

        }else if (bOrder.getOrderdate() != null && !"".equals(bOrder.getOrderdate())) {
            builder.append(" and orderdate like '%" + bOrder.getOrderdate()  + "%'");
        }
        builder.append(" ORDER BY orderdate DESC LIMIT ?,?");
        List<Map> maps = DBUtil.queryListMap(builder.toString(),(bOrder.getPage() - 1) * bOrder.getLimit(), bOrder.getLimit());
        return maps;
    }

    /**
     * 查看所有驳回订单的数据数量
     *
     * @param bOrder
     * @return
     */
    @Override
    public int rejectCount(BOrder bOrder) {
        StringBuilder builder=new StringBuilder("SELECT count(*) FROM b_order where orderstatus= 2");
        if (bOrder.getW_name() != null && !"".equals(bOrder.getW_name())) {
            builder.append(" and w_name like '%" + bOrder.getW_name() + "%'");

        }else if (bOrder.getE_name() != null && !"".equals(bOrder.getE_name())) {
            builder.append(" and e_name like '%" + bOrder.getE_name() + "%'");

        }else if (bOrder.getOrderdate() != null && !"".equals(bOrder.getOrderdate())) {
            builder.append(" and orderdate like '%" + bOrder.getOrderdate()  + "%'");
        }
        builder.append(" ORDER BY orderdate DESC ");
        List<List> lists = DBUtil.queryList(builder.toString());
        Object o = lists.get(0).get(0);
        int i = Integer.parseInt(o + "");
        return i;
    }


    /**
     * 通过主键的删除
     *
     * @param id
     * @return
     */
    @Override
    public int deleteById(Integer id) {
        String sql = "delete from b_order where orderid=?";
        int i = DBUtil.update(sql, id);
        return i;
    }

    /**
     * 获取历史的订单
     *
     * @param bOrder
     * @return
     */
    @Override
    public List<Map> getHOrder(BOrder bOrder) {
        StringBuilder builder = new StringBuilder("select orderid,plancount,orderstatus,orderdate,e_name,p_name,w_name FROM b_order where  orderstatus= 3");
        List<Map> maps;
        if (bOrder.getUsername() != null && !"".equals(bOrder.getUsername())) {
            builder.append(" and e_name like '%" + bOrder.getUsername() + "%'");
            builder.append(" limit ?,?");
            maps = DBUtil.queryListMap(builder.toString(),(bOrder.getPage() - 1) * bOrder.getLimit(), bOrder.getLimit());

        } else {
            builder.append(" limit ?,?");
            maps = DBUtil.queryListMap(builder.toString(),(bOrder.getPage() - 1) * bOrder.getLimit(), bOrder.getLimit());

        }
        return maps;
    }



    /**
     * 显示订单未审核的表
     * Failed=未审核
     * @param
     * @return
     */
    @Override
    public List<Map> getFailedOrder(BOrder bOrder) {
        StringBuilder builder=new StringBuilder("SELECT * FROM b_order where orderstatus= 0");
        if (bOrder.getW_name() != null && !"".equals(bOrder.getW_name())) {
            builder.append(" and w_name like '%" + bOrder.getW_name() + "%'");

        }else if (bOrder.getOrderdate() != null && !"".equals(bOrder.getOrderdate())) {
            builder.append(" and orderdate like '%" + bOrder.getOrderdate()  + "%'");
        }
        builder.append(" ORDER BY orderdate DESC LIMIT ?,?");
        List<Map> maps = DBUtil.queryListMap(builder.toString(),(bOrder.getPage() - 1) * bOrder.getLimit(), bOrder.getLimit());
        return maps;
    }
    @Override
    public int getFailedOrderCount(BOrder bOrder) {
        StringBuilder builder=new StringBuilder("SELECT count(*) FROM b_order where orderstatus= 0");
        if (bOrder.getW_name() != null && !"".equals(bOrder.getW_name())) {
            builder.append(" and w_name like '%" + bOrder.getW_name() + "%'");

        }else if (bOrder.getOrderdate() != null && !"".equals(bOrder.getOrderdate())) {
            builder.append(" and orderdate like '%" + bOrder.getOrderdate()  + "%'");
        }
        builder.append(" ORDER BY orderdate DESC ");
        List<List> lists = DBUtil.queryList(builder.toString());
        Object o = lists.get(0).get(0);
        int i = Integer.parseInt(o + "");
        return i;
    }

    /**
     * 将订单状态改为通过
     *
     * @param order
     * @return
     */
    @Override
    public int updateStatus(BOrder order) {

        String sql = "UPDATE b_order SET orderstatus=1,e_name=? where orderid=?";
        int update = DBUtil.update(sql, order.getE_name(), order.getOrderid());
        return update;
    }
    /**
     * 提交入库申请
     *
     * @param order
     * @return
     */
    @Override
    public int updateStatus3(BOrder order) {

        String sql = "UPDATE b_order SET orderstatus=3,p_name=? where orderid=?";
        System.out.println(order.getOrderid());
        System.out.println(order.getP_name());
        int update = DBUtil.update(sql, order.getP_name(), order.getOrderid());
        return update;
    }

    /**
     * 将订单全部执行，入库，并且将订单中的数量放入仓库
     * @param order
     * @return
     */
    @Override
    public int updateStatus4(BOrder order){

        String sql="select bookid,b_count from b_order_wj WHERE orderid=?";
        List<Map> map = DBUtil.queryListMap(sql, order.getOrderid());
        //通过点单好获取book信息，进行书籍库存的更改
        for (Map map1 : map) {
            int bookid = (int) map1.get("bookid");
            int count1 = (int) map1.get("b_count");
            String sql1="select count from book where bookid=?";
            List<List> lists = DBUtil.queryList(sql1, bookid);
            int count2 = (int) lists.get(0).get(0);

            int count=count1+count2;

            String sql2="update book set count=? where bookid=?";
            DBUtil.update(sql2,count,bookid);

        }
        String sql3="update b_order set orderstatus=4,storagedate=? where orderid=?";

        int update = DBUtil.update(sql3,order.getStoragedate(),order.getOrderid());
        return update;

    }

    /**
     * 将订单设为驳回状态
     * status=2
     *
     * @param bOrder
     * @return
     */
    @Override
    public int setStatus2(BOrder bOrder) {
        String sql = "UPDATE b_order b SET b.orderstatus=2,b.bh=?,b.e_name=? WHERE b.orderid=?";
        int update = DBUtil.update(sql, bOrder.getBh(), bOrder.getE_name(), bOrder.getOrderid());
        return update;
    }

    /**
     * 将驳回订单再次提交
     *
     * @param order
     * @return
     */
    @Override
    public int updateStatus0(BOrder order) {
        String sql="UPDATE b_order SET orderstatus=0 where orderid=?";
        int update = DBUtil.update(sql,order.getOrderid());
        return update;
    }

    /**
     * 修改驳回订单的数据
     *
     * @param bOrderWj
     * @return
     */
    @Override
    public int updateReject(BOrderWj bOrderWj) {

        int bookid = bOrderWj.getBookid();
        String sql1="select inprice from book where bookid=?";
        List<List> lists = DBUtil.queryList(sql1, bookid);
        double inprice = (double) lists.get(0).get(0);
        String sql="update b_order_wj set count=?,t_price=? where orderid=? and bookid=?";
        int i = DBUtil.update(sql, bOrderWj.getCount(),bOrderWj.getCount()*inprice, bOrderWj.getOrderid(), bOrderWj.getBookid());
        return i;
    }

    /**
     * 修改驳回状态的订单表数量
     *
     * @param bOrder
     * @return
     */
    @Override
    public int updateCount(BOrder bOrder) {

        String sql="update b_order set plancount=? where orderid=?";
        int i = DBUtil.update(sql, bOrder.getPlancount(), bOrder.getOrderid());
        return i;
    }

    /**
     * 修改实际采购入库书籍数量
     * @param bOrderWj
     * @return
     */
    @Override
    public double updateCOunt1(BOrderWj bOrderWj) {
        String s = "select inprice from book where bookid=?";
        List<List> lists = DBUtil.queryList(s,bOrderWj.getBookid());
        Object o = lists.get(0).get(0);
        double v = Double.parseDouble(o + "");
        String sql = "update b_order_wj set b_count = ?, t_price = ? where bookid = ? and orderid=?";
        int i = DBUtil.update(sql, bOrderWj.getB_count(), (bOrderWj.getB_count()*bOrderWj.getInprice()),bOrderWj.getBookid(),bOrderWj.getOrderid());
        return  bOrderWj.getB_count()*bOrderWj.getInprice();
    }

    /**
     * 修改总订单的数量/价格/采购人信息
     * @param bOrder
     * @return
     */
    @Override
    public int updateBCount(BOrder bOrder) {
        String sql = "update b_order set  actualcount = ?, b_t_price = ? , orderstatus=3, p_name = ? where  orderid = ?";
        int i = DBUtil.update(sql, bOrder.getActualcount(), bOrder.getB_t_price(),bOrder.getP_name(), bOrder.getOrderid());
        return i;
    }

    /**
     * 获得历史订单的数量
     *
     * @param bOrder
     * @return
     */
    @Override
    public int getHBOrderCount(BOrder bOrder) {
        String sql = "select count(*) FROM b_order where orderstatus= 3";
        List<List> lists = DBUtil.queryList(sql);
        Object o = lists.get(0).get(0);
        int i = Integer.parseInt(o + "");
        return i;

    }
}
